import os
import numpy as np
import pandas as pd
import cx_Oracle as oracle
# from datetime import date, timedelta
# Today = date.today().strftime('%Y%m%d')
# Yesterday = (date.today() + timedelta(days = -1)).strftime('%Y%m%d')

PATH = 'Z:\\毛衡工作记录\\202103股票产品打新业务\\202104打新产品台账\\valuation\\'
OUTPUTPATH = 'Z:\\估值表反推流水\\成交流水.xlsx'
Yesterday = '20210415'
Today = '20210416'
Prefix = '盈升'
OracleServer={'default':'yspread/Y*iaciej123456@172.17.21.3:1521/WDZX'}
########################################################################

#----------------------------------------------------------------------
#输入code=600000.SH，startdate=yyyyMMdd，endDate=yyyyMMdd
def getDailyDataByDateFromOracleServer(code,startDate,endDate):
    #获取行情数据
    #附注status -1:交易-2:待核查0:停牌XD:除息XR:除权DR:除权除息N:上市首日
    connection = oracle.connect(OracleServer['default'])
    cursor = connection.cursor()
    oracleStr="select  S_INFO_WINDCODE as code,TRADE_DT as \"date\",S_DQ_OPEN as open,S_DQ_HIGH as high,S_DQ_LOW as low,S_DQ_CLOSE as close,S_DQ_PRECLOSE as preClose,S_DQ_VOLUME as volume,S_DQ_AMOUNT as amount,S_DQ_CHANGE as change,S_DQ_PCTCHANGE as pctChange,S_DQ_ADJFACTOR as adjFactor,S_DQ_AVGPRICE as vwap,S_DQ_TRADESTATUS as status from wind_filesync.AShareEODPrices where S_INFO_WINDCODE='{0}' and TRADE_DT>={1} and TRADE_DT<={2} order by TRADE_DT".format(code,startDate,endDate)
    cursor.execute(oracleStr)
    mydata = cursor.fetchall()
    mydata = pd.DataFrame(mydata,columns=['code','date','open','high','low','close','preClose','volume','amount','change','pctChange','adjFactor','vwap','status'])
    mydata[['open','high','low','close','preClose','volume','amount','change','pctChange','adjFactor','vwap']] = mydata[['open','high','low','close','preClose','volume','amount','change','pctChange','adjFactor','vwap']].astype('float')
#    codeList = []
#    for code in mydata['code'].values:
#        code = code[:-3]
#        codeList.append(code) 
#    mydata['code'] = codeList
    return mydata

########################################################################
'''
说明：
（1）LoadFiles_Stock用来加载excel估值表文件，识别类型源作为前缀，日期作为后缀的excel文件。
（2）PATH 是excel的保存路径。输出读取excel后的DataFrame，共计4个。
（3）dF_today, dF_yesterday 是源文件的直接副本。 Stock_today, Stock_yesterday 筛选出了股票。
'''

def LoadFiles_Stock(Yesterday, Today, PATH, Prefix):
    files = os.listdir(PATH)
    FileList = []
    if Prefix == '汇安':
        for file in files:
            if file[:2] == Prefix and (file[-12:-4] == Yesterday or file[-12:-4] == Today):
                FileList.append(file)
    elif Prefix == '盈升':
        for file in files:
            if file[:2] == Prefix and (file[-13:-5] == Yesterday or file[-13:-5] == Today):
                FileList.append(file)
    FileList = sorted(FileList)
    if len(FileList) != 2:
        print('Error: Source files cannot be found!')
    YesterdayEvaluation = FileList[0]
    dF_yesterday = pd.read_excel(PATH + YesterdayEvaluation)
    StockList = []
    for stock in dF_yesterday['科目代码'].values:
        stockcode = str(stock)
        if len(stockcode) == 14 and stockcode[:4] == '1102':
            dF_yesterday.loc[dF_yesterday['科目代码'] == stock, '科目代码'] = stockcode[-6:]
            StockList.append(stockcode[-6:])
    Stock_yesterday = dF_yesterday[dF_yesterday['科目代码'].isin(StockList)]
    TodayEvaluation = FileList[1]
    dF_today = pd.read_excel(PATH + TodayEvaluation)
    StockList = []
    for stock in dF_today['科目代码'].values:
        stockcode = str(stock)
        if len(stockcode) == 14 and stockcode[:4] == '1102':
            dF_today.loc[dF_today['科目代码'] == stock, '科目代码'] = stockcode[-6:]
            StockList.append(stockcode[-6:])
    Stock_today = dF_today[dF_today['科目代码'].isin(StockList)]
    return dF_today, dF_yesterday, Stock_today, Stock_yesterday

########################################################################
'''
说明：
（1）DataFilter_Stock用来过滤和处理LoadFiles_Stock输出的股票DataFrame。
（2）OUTPATH 是生成的交易流水excel的保存路径。
（3）推理思路：股票数量变动就放入TradeStockDict里，并从DataFrame和oracle中获取相应数据。
               按公式：交易损益变动 = 总市值变动 - 持仓损益变动，计算出交易损益，按市值
               作为权重平摊到TradeStockDict中每一只股票上。
'''

def DataFilter_Stock(Yesterday, Today, PATH, OUTPUTPATH, Prefix):

    dF_today, dF_yesterday, Stock_today, Stock_yesterday = LoadFiles_Stock(Yesterday, Today, PATH, Prefix)
    NetValueToday = dF_today[dF_today['科目代码'] == '资产资产净值：']['市    值'].values[0]
    NetValueYesterday = dF_yesterday[dF_yesterday['科目代码'] == '资产资产净值：']['市    值'].values[0]
    dNV = NetValueToday - NetValueYesterday


    # Case1：数量发生变化
    strr = '待定'
    TradeStockDict = {}
    TradeStockDict['发生日期'] = []
    TradeStockDict['基金名称'] = []
    TradeStockDict['证券代码'] = []
    TradeStockDict['证券名称'] = []
    TradeStockDict['委托方向'] = []
    TradeStockDict['成交数量'] = []
    TradeStockDict['成交金额'] = []
    TradeStockDict['成交均价'] = []
    TradeStockDict['昨收价格'] = []
    TradeStockDict['交易市场'] = []
    TradeStockDict['资产市值'] = []

    for stock in Stock_today['科目代码'].values:
        if stock not in Stock_yesterday['科目代码'].values:
            if Stock_today[Stock_today['科目代码']==stock]['停牌信息'].values[0] == '【正常交易】':
                DATE = Today
                CONSTRACT = Prefix
                ID = stock
                IDNAME = Stock_today[Stock_today['科目代码'] == stock]['科目名称'].values[0]
                DIRECTION = '买入'
                NUMBER = -Stock_today[Stock_today['科目代码'] == stock]['数    量'].values[0]
                if stock[:3] == '600' or stock[:3] == '601' or stock[:3] == '603' or stock[:3] == '688':
                    MARKET = '上交所A'
                    code = stock + '.SH'
                elif stock[:3] == '000' or stock[:3] == '001' or stock[:3] == '002' or stock[:3] == '300':
                    MARKET = '深交所A'
                    code = stock + '.SZ'
                else:
                    MARKET = strr
                #NV = Stock_today[Stock_today['科目代码'] == stock]['市    值'].values[0]
                mydata = getDailyDataByDateFromOracleServer(code,Yesterday,Today)
                try:
                    CLOSE = mydata[mydata['date']==Today]['close'].values[0]
                except:
                    CLOSE = 0
                try:
                    PREVCLOSE = mydata[mydata['date']==Yesterday]['close'].values[0]
                except:
                    PREVCLOSE = 0
                try:
                    NV = CLOSE * abs(NUMBER)
                except:
                    NV = 0
                try:
                    TRADETOTAL = CLOSE * NUMBER
                except:
                    TRADETOTAL = 0
                TradeStockDict['发生日期'].append(DATE[:4]+'-'+DATE[4:6]+'-'+DATE[6:])
                TradeStockDict['基金名称'].append(CONSTRACT)
                TradeStockDict['证券代码'].append(ID)
                TradeStockDict['证券名称'].append(IDNAME)
                TradeStockDict['委托方向'].append(DIRECTION)
                TradeStockDict['成交数量'].append(NUMBER)
                TradeStockDict['成交金额'].append(TRADETOTAL)
                TradeStockDict['成交均价'].append(CLOSE)
                TradeStockDict['昨收价格'].append(PREVCLOSE)
                TradeStockDict['交易市场'].append(MARKET)
                TradeStockDict['资产市值'].append(NV)
        else:
            if Stock_today[Stock_today['科目代码']==stock]['停牌信息'].values[0] == '【正常交易】' and Stock_yesterday[Stock_yesterday['科目代码']==stock]['停牌信息'].values[0] == '【正常交易】':
                StockNumToday = Stock_today[Stock_today['科目代码'] == stock]['数    量'].values[0]
                StockNumYesterday = Stock_yesterday[Stock_yesterday['科目代码'] == stock]['数    量'].values[0]
                DATE = Today
                CONSTRACT = Prefix
                ID = stock
                IDNAME = Stock_today[Stock_today['科目代码'] == stock]['科目名称'].values[0]
                if StockNumToday - StockNumYesterday > 0:
                    DIRECTION = '买入'
                elif StockNumToday - StockNumYesterday < 0:
                    DIRECTION = '卖出'
                else:
                    continue
                NUMBER = StockNumYesterday - StockNumToday
                if stock[:3] == '600' or stock[:3] == '601' or stock[:3] == '603' or stock[:3] == '688':
                    MARKET = '上交所A'
                    code = stock + '.SH'
                elif stock[:3] == '000' or stock[:3] == '001' or stock[:3] == '002' or stock[:3] == '300':
                    MARKET = '深交所A'
                    code = stock + '.SZ'
                else:
                    MARKET = strr
                #NV = Stock_today[Stock_today['科目代码'] == stock]['市    值'].values[0]
                mydata = getDailyDataByDateFromOracleServer(code,Yesterday,Today)
                try:
                    CLOSE = mydata[mydata['date']==Today]['close'].values[0]
                except:
                    CLOSE = 0
                try:
                    PREVCLOSE = mydata[mydata['date']==Yesterday]['close'].values[0]
                except:
                    PREVCLOSE = 0
                try:
                    NV = CLOSE * abs(StockNumToday)
                except:
                    NV = 0
                try:
                    TRADETOTAL = CLOSE * NUMBER
                except:
                    TRADETOTAL = 0
                TradeStockDict['发生日期'].append(DATE[:4]+'-'+DATE[4:6]+'-'+DATE[6:])
                TradeStockDict['基金名称'].append(CONSTRACT)
                TradeStockDict['证券代码'].append(ID)
                TradeStockDict['证券名称'].append(IDNAME)
                TradeStockDict['委托方向'].append(DIRECTION)
                TradeStockDict['成交数量'].append(NUMBER)
                TradeStockDict['成交金额'].append(TRADETOTAL)
                TradeStockDict['成交均价'].append(CLOSE)
                TradeStockDict['昨收价格'].append(PREVCLOSE)
                TradeStockDict['交易市场'].append(MARKET)
                TradeStockDict['资产市值'].append(NV)


    for stock in Stock_yesterday['科目代码'].values:
        if stock not in Stock_today['科目代码'].values:
            DATE = Today
            CONSTRACT = Prefix
            ID = stock
            IDNAME = Stock_yesterday[Stock_yesterday['科目代码'] == stock]['科目名称'].values[0]
            DIRECTION = '卖出'
            NUMBER = Stock_yesterday[Stock_yesterday['科目代码'] == stock]['数    量'].values[0]
            if stock[:3] == '600' or stock[:3] == '601' or stock[:3] == '603' or stock[:3] == '688':
                MARKET = '上交所A'
                code = stock + '.SH'
            elif stock[:3] == '000' or stock[:3] == '001' or stock[:3] == '002' or stock[:3] == '300':
                MARKET = '深交所A'
                code = stock + '.SZ'
            else:
                MARKET = strr
            #NV = Stock_today[Stock_today['科目代码'] == stock]['市    值'].values[0]
            mydata = getDailyDataByDateFromOracleServer(code,Yesterday,Today)
            try:
                CLOSE = mydata[mydata['date']==Today]['close'].values[0]
            except:
                CLOSE = 0
            try:
                PREVCLOSE = mydata[mydata['date']==Yesterday]['close'].values[0]
            except:
                PREVCLOSE = 0
            try:
                NV = CLOSE * abs(NUMBER)
            except:
                NV = 0
            try:
                TRADETOTAL = CLOSE * NUMBER
            except:
                TRADETOTAL = 0
            TradeStockDict['发生日期'].append(DATE[:4]+'-'+DATE[4:6]+'-'+DATE[6:])
            TradeStockDict['基金名称'].append(CONSTRACT)
            TradeStockDict['证券代码'].append(ID)
            TradeStockDict['证券名称'].append(IDNAME)
            TradeStockDict['委托方向'].append(DIRECTION)
            TradeStockDict['成交数量'].append(NUMBER)
            TradeStockDict['成交金额'].append(TRADETOTAL)
            TradeStockDict['成交均价'].append(CLOSE)
            TradeStockDict['昨收价格'].append(PREVCLOSE)
            TradeStockDict['交易市场'].append(MARKET)
            TradeStockDict['资产市值'].append(NV)

    if list(TradeStockDict.values()) == [[], [], [], [], [], [], [], [], [], [], []]:
        print('当天流水为空，请检查后重试！')
    else:
        df = pd.DataFrame(TradeStockDict,index = list(range(len(list(TradeStockDict.values())[0]))))
        #netvalueList = list(df['资产市值'])
        NV_total = sum(df['资产市值'])
        df['资产市值'] = np.multiply(np.array(list(df['资产市值'])),1/NV_total)
        #df.rename(columns={'资产市值': '损益'}, inplace=True)
        diff = sum(np.multiply(np.array(list(df['成交数量'])),(np.array(list(df['成交均价']))-np.array(list(df['昨收价格'])))))
        df['资产市值'] = np.multiply(np.array(list(df['资产市值'])),(dNV-diff))
        df['成交均价'] = np.array(list(df['成交均价'])) + np.multiply(1/np.array(list(df['成交数量'])),np.array(list(df['资产市值'])))
        toExcel = df[['发生日期','基金名称','证券代码','证券名称','委托方向','成交数量','成交金额','成交均价','交易市场']]
        #toExcel['资产市值'] = netvalueList
        toExcel['成交数量'] = abs(toExcel['成交数量'].values)
        toExcel['成交金额'] = np.multiply(np.array(list(toExcel['成交数量'])),np.array(list(toExcel['成交均价'])))
        toExcel['成交均价'] = round(toExcel['成交均价'], 4)
        toExcel['成交金额'] = round(toExcel['成交金额'], 4)
        toExcel.to_excel(OUTPUTPATH, encoding = 'utf-8', index = False, header = True)


if __name__ == '__main__':
    PATH = 'Z:\\毛衡工作记录\\202103股票产品打新业务\\202104打新产品台账\\valuation\\'
    OUTPUTPATH = 'Z:\\估值表反推流水\\成交流水.xlsx'
    Yesterday = '20210616'
    Today = '20210617'
    Prefix = '盈升'
    DataFilter_Stock(Yesterday, Today, PATH, OUTPUTPATH, Prefix)